<?php
/**
 * Excel数据导入模板生成控制器
 * @author 聂哥
 * @Date: 2023-01-13 18:41:12
*/
namespace app\admin\controller\supply;

use app\common\controller\CrmController;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use think\facade\Env;

class Tpl extends CrmController
{
    protected $spreadsheet;

    // 需要过滤掉的字段
    protected $filter_column = [
        'customer' => ['id', 'deal_status', 'deal_time', 'is_lock', 'create_time','delete_time', 'update_time', 'delete_time', 'collect_time']
    ];

    // 限制做下拉的字段
    protected $limitSelectFields = [
        'level_id' => '普通客户,优质客户,重要客户',
        'industry_id' => '房地产,金融,互联网'
    ];

    /**
     * 初始化
     * @MethodAuthor: 聂哥
     * @Date: 2023-01-14 11:17:40
     */
    public function initialize()
    {
        $this->spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    }

    /**
     * 批量导入
     * @MethodAuthor: 聂哥
     * @Date: 2023-01-13 18:40:56
     */
    public function customerImport($title = '批量导入客户模板',$maxRows = 200)
    {
        $sheet = $this->spreadsheet->getActiveSheet();
        $sheet -> setTitle($title);//设置sheet的名称
        // 获取数据表字段详情
        $list = $this->getFieldInfoByTable('supply_quoted');
        // 生成模板表头
        $i = 0;
        foreach ($list as $k => $v) {
            if (in_array($v['COLUMN_NAME'], $this->filter_column['customer'])) continue;
            if ($i >= 26) {
                $cell = chr(65 + $i / 26 - 1) . chr(65 + $i % 26);
            } else {
                $cell = chr(65 + $i);
            }
            $sheet->setCellValue($cell . '1', $v['COLUMN_COMMENT']);
            $i++;

            // 给后面数据添加下拉列表限制约束
            $field = $v['COLUMN_NAME'];
            if( array_key_exists($field,$this->limitSelectFields) ){
                $options = $this->limitSelectFields[$field];
                for($r = 2;$r<=$maxRows;$r++){
                    $this->setValidation($sheet, $cell . $r, '"'.$options.'"');
                }
            }
        }
        
        $this->save("quotedtpl" . date('Ymd'));
        exit;

    }

    /**
     * 获取导出字段
     * @MethodAuthor: 聂哥
     * @Date: 2023-01-14 10:10:36
     */
    public function getFieldInfoByTable($tabname,$database = null)
    {
        $table = 'yd_' . $tabname;
        if( !$database ){
            $database = Env::get('database.database');
        }
        $fieldArr = [];
        $list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]);
        return $list;
    }

    /**
     * 生成Excel模板
     * @MethodAuthor: 聂哥
     * @Date: 2023-01-14 10:34:56
     */
    protected function save($name)
    {
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        //header('Content-Type:application/vnd.ms-excel');//输出03版本
        header("Content-Disposition: attachment;filename=$name" . '.xlsx');
        header('Cache-Control: max-age=0');
        header('Cache-Control: cache, must-revalidate');
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($this->spreadsheet, 'Xlsx');
        try {
            $writer->save('php://output');
            $this->spreadsheet->disconnectWorksheets();
            unset($spreadsheet);
        } catch (\Exception $e) {
            echo $e->getMessage();
        }
    }

    /**
     * 设置某个单元格的下拉列表规则
     * @param Worksheet $sheet
     * @param [string] $cellPoint 单元格坐标. A1
     * @param [sting] $format 公式
     * @return void
     * @MethodAuthor: 聂哥
     * @Date: 2023-01-14 10:10:36
     */
    protected function setValidation(Worksheet $sheet, $cellPoint, $format)
    {
        $validation = $sheet -> getCell($cellPoint) -> getDataValidation();
        $validation -> setType(DataValidation::TYPE_LIST);
        $validation -> setErrorStyle(DataValidation::STYLE_INFORMATION);
        $validation -> setAllowBlank(false);
        $validation -> setShowInputMessage(true);
        $validation -> setShowErrorMessage(true);
        $validation -> setShowDropDown(true);
        $validation -> setErrorTitle('输出错误');
        $validation -> setError('值不在列表中');
        $validation -> setPromptTitle('请选择');
        $validation -> setFormula1($format);
        // $validation->setFormula1('=major');//使用某个公式
        // $validation->setFormula1('"A1,A2,A3"');//可以直接写列表，用逗号分隔,最多255字符
        // $objValidation->setFormula1('=INDIRECT(F5)');//indirect间接的，以某个坐标为依托，联级
    }
}
